#!/usr/bin/env python
# encoding: utf-8
#################
# Name:        更新用户行为数据( 一个月内 新闻评论数量.....)
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone import Phone
from birth import Birth


reload(sys)
sys.setdefaultencoding('utf-8')
sys.path.append(os.environ['HOME']+"/lib")
from pyctlib import *

VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg

    self.__table       = 'yd_user_act_summary'
    self.__add_user_id = 1
    self.__month       = 60
    


  def handle_data(self):
    #sql = "select * from usr_user_datasource_rel where user_business_id=-1"
    sql = "select * from usr_user_datasource_rel"
    res = self.__select_db(sql, True)
    if res is None:
      logging.info('sql:%s. get data is none'%(sql))
      return
    for re in res:
      if re.get('user_id') and re.get('user_business_id'):
        re['user_id']          = int(re.get('user_id'))
        re['user_business_id'] = int(re.get('user_business_id'))  
        logging.info('user_id:%d user_business_id:%d'%(re.get('user_id'), \
                           re.get('user_business_id')))
        cnt_user =  self.__table_cnt_count( 'usr_user_basic_info', \
                                             "user_id=%s"%( re['user_id']) )
        if cnt_user == 0:
          continue
        self.month1_app_review_cnt(re)
        self.month1_app_pub_qingbao_cnt(re)
        self.month1_app_review_qingbao_cnt(re)
        self.month1_app_scorehis_cnt(re)
        self.last_app_login_time(re)
        self.month1_app_open_cnt(re)
   
  #近一月APP评论文章数
  def month1_app_review_cnt(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    n_days = self.__get_before_ndays( self.__month )
    wh="DATE_FORMAT(sub_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and user_id={} ".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )
    cnt = self.__table_cnt_count( 'yd_news_comment', wh)
    
    if cnt == 0:
      return
    add_dict= {}
    add_dict['user_key']              = data_dict.get('user_id')
    add_dict['month1_app_review_cnt'] = cnt

    if len(add_dict)<1:
      return
    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id')))
   
    if cnt_user==0:
      sql = self.__generate_insert_sql(add_dict)
      if sql: 
       self.__update_db(sql)
    else:
      sql = self.__generate_update_sql(add_dict)
      if sql: 
       self.__update_db(sql)
    

   #'近一月APP发布情报数',
  def month1_app_pub_qingbao_cnt(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    n_days = self.__get_before_ndays( self.__month )
    wh= "DATE_FORMAT(create_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and create_id={} ".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )
    cnt = self.__table_cnt_count( 'yd_intelligence_info', wh)                     
    
    if cnt == 0:                                                             
      return                                                                 
    add_dict= {}                                                             
    add_dict['user_key']              = data_dict.get('user_id')             
    add_dict['month1_app_pub_qingbao_cnt'] = cnt                                  

    if len(add_dict)<1:
      return
    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id')))

    if cnt_user==0:                                                          
      sql = self.__generate_insert_sql(add_dict)                             
      if sql:                                                                
       self.__update_db(sql)                                                 
    else:                                                                    
      sql = self.__generate_update_sql(add_dict)                             
      if sql:                                                                
       self.__update_db(sql)  
  
   #'一月APP评论情报数',
  def month1_app_review_qingbao_cnt(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    n_days = self.__get_before_ndays( self.__month )
    wh= "DATE_FORMAT(create_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and create_id={} ".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )
    cnt = self.__table_cnt_count( 'yd_intelligence_reply_list', wh)
    
    if cnt == 0:
      return
    add_dict= {}
    add_dict['user_key']              = data_dict.get('user_id')
    add_dict['month1_app_review_qingbao_cnt'] = cnt

    if len(add_dict)<1:
      return

    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id')))
    if cnt_user==0:
      sql = self.__generate_insert_sql(add_dict)
      if sql:
       self.__update_db(sql)
    else:
      sql = self.__generate_update_sql(add_dict)
      if sql:
       self.__update_db(sql)

   #'一月用户签到数',
  def month1_app_scorehis_cnt(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    n_days = self.__get_before_ndays( self.__month )
    wh= "DATE_FORMAT(score_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and client_id={} ".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )
    cnt = self.__table_cnt_count( 'yd_client_user_scorehis', wh)
    
    if cnt == 0:
      return
    add_dict= {}                                                             
    add_dict['user_key']              = data_dict.get('user_id')             
    add_dict['month1_app_scorehis_cnt'] = cnt                   
    
    if len(add_dict)<1:
      return
    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id')))   
    if cnt_user==0:                                                          
      sql = self.__generate_insert_sql(add_dict)                             
      if sql:                                                                
       self.__update_db(sql)                                                 
    else:                                                                    
      sql = self.__generate_update_sql(add_dict)                             
      if sql:                                                                
       self.__update_db(sql) 

  #近一月最后一次登录时间:
  def last_app_login_time(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    n_days = self.__get_before_ndays( self.__month )
    wh= "DATE_FORMAT(login_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and user_id={} \
        order by login_time desc limit 1".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )
    
    query = "select * from yd_client_device_login  where %s "%(wh) 
    res = self.__select_db( query, True)
    if res is None:
      return
    #logging.info(res)
    if len(res) ==0:
      return
    if not res[0].get('login_time'):
      return 
    add_dict= {}
    add_dict['user_key']            = data_dict.get('user_id')
    add_dict['last_app_login_time'] = res[0].get('login_time') 
 
    if len(add_dict)<1:
      return
    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id'))) 
    if cnt_user==0:
      sql = self.__generate_insert_sql(add_dict)
      if sql:
       self.__update_db(sql)
    else:
      sql = self.__generate_update_sql(add_dict)
      if sql:
       self.__update_db(sql)


   #'一月用户app 每个时间打开次数,
  def month1_app_open_cnt(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return

    n_days = self.__get_before_ndays( self.__month )
    wh= "DATE_FORMAT(login_time,'%Y-%m-%d %H:%M:%S') >\
        DATE_FORMAT('{}', '%Y-%m-%d %H:%M:%S') and user_id={}".format(\
        n_days.strftime('%y-%m-%d %H:%M:%S'), data_dict.get('user_business_id') )

    query = "select * from yd_client_device_login  where %s "%(wh)
    res = self.__select_db( query, True)
    if res is None:
      return

    cnt_1 = 0
    cnt_2 = 0
    cnt_3 = 0
    cnt_4 = 0
    cnt_5 = 0
    cnt_6 = 0
    cnt_7 = 0
    cnt_month = 0 
    for re in res:
      if re.get('login_time') is None:
        continue
      dt = datetime.datetime.strptime( re.get('login_time'),'%Y-%m-%d %H:%M:%S')
      if dt.hour>=0 and dt.hour<=6:
        cnt_1 += 1
      elif dt.hour >6 and dt.hour <=9:
        cnt_2 += 1
      elif dt.hour >9 and dt.hour<=12:
        cnt_3 += 1
      elif dt.hour>12 and dt.hour<=14:                                         
        cnt_4 += 1                                                             
      elif dt.hour>14 and dt.hour<=18:                                         
        cnt_5 += 1                                                             
      elif dt.hour>18 and dt.hour<=22:                                         
        cnt_6 += 1                                                             
      elif dt.hour>22 and dt.hour<24:                                          
        cnt_7 += 1                                                             
      cnt_month +=1 

    add_dict= {}
    add_dict['user_key']            = data_dict.get('user_id')
    if cnt_1 >0:
      add_dict['month1_app_open026_cnt'] = cnt_1
    if cnt_2 >0:
      add_dict['month1_app_open629_cnt'] = cnt_2 
    if cnt_3 >0:
      add_dict['month1_app_open9212_cnt'] = cnt_3
    if cnt_4 >0:
      add_dict['month1_app_open12214_cnt'] = cnt_4
    if cnt_5 >0:
      add_dict['month1_app_open14218_cnt'] = cnt_5
    if cnt_6 >0:
      add_dict['month1_app_open18222_cnt'] = cnt_6
    if cnt_7 >0:
      add_dict['month1_app_open22224_cnt'] = cnt_7
    if cnt_month>0:
      add_dict['month1_app_open_cnt'] = cnt_month
   

    if len(add_dict) <1:
      return
    cnt_user = self.__table_cnt_count('yd_user_act_summary', 'user_key=%d'%(\
                                      data_dict.get('user_id'))) 
    if cnt_user==0:
      sql = self.__generate_insert_sql(add_dict)
      if sql:
       self.__update_db(sql)
    else:
      sql = self.__generate_update_sql(add_dict)
      if sql:
       self.__update_db(sql)



  #generate insert sql
  def __generate_insert_sql(self, add_dict ):
    if type(add_dict) != dict or len(add_dict) <1:
      return

    insert_sql = "insert into %s ( "%(self.__table )
    values_sql = "values ("
    for key, val in add_dict.items():
      insert_sql += key + ','
      if type(val) == int:
        values_sql += '%d'%(val) + ','
      elif type(val) == float:
        values_sql += '%f'%(val) + ','
      else:
        values_sql += "'%s'"%(val) +','
    insert_sql += "add_user_id, add_time" +','
    values_sql += "%d, '%s' "%(1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +','
    if insert_sql[-1] == ',':
      insert_sql = insert_sql[:-1] + ') '
    if values_sql[-1]==',':
      values_sql = values_sql[:-1] + ') '

    sql = insert_sql + values_sql
    return sql

  #generate sql                                                              
  def __generate_update_sql(self, add_dict ):
    if type(add_dict) != dict or len(add_dict) <1:
      return

    if not add_dict.get('user_key'):
      return

    wh = "where user_key = '%s'"%(add_dict['user_key'])
    update_key = 'user_key'

    update_sql = "update %s  set "%( self.__table )                                          
    for key, val in add_dict.items():                                        
                                                                             
      if key == update_key:                                                  
        continue                                                             
      if type(val) == int:                                                   
        update_sql +=  '%s = %d'%(key, val) + ','                            
      elif type(val) == float:                                               
        update_sql += ' %s = %f'%(key, val) + ','                            
      else:                                                                  
        update_sql += "%s = '%s'"%(key, val) +','                            
    update_sql += "update_user_id = %d, update_time='%s' "%(1, \
                 datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +',' 
    if update_sql[-1]==',':                                                  
      update_sql = update_sql[:-1]                                           
    update_sql += wh                                                         

    return update_sql  




	
  #通过select count 查询数据
  def __table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
   
    #print(sql) 
    res = self.__select_db(sql)                                              
    #print( res )                                                            
    return res[0][0]  
  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      logging.info(sql)
      self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        

      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  

  def __get_before_ndays(self, days):
    now = datetime.datetime.now()
    delta = datetime.timedelta(days)
    n_days = now - delta
    return n_days


   

def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   obj = Handle(UDB,cfg)                                                     
   obj.handle_data() 
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
